package persistence;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.Map.Entry;
import java.util.Set;

import utilities.Logger;

import cdautils.TriageTag;
import cdautils.TriageTag.BodyPart;
import cdautils.TriageTag.Gender;
import cdautils.TriageTag.PhaseOfLife;
import cdautils.TriageTag.Stability;
import cdautils.TriageTag.TreatmentStatus;
import cdautils.TriageTag.TriageCategory;

/**
 * HSQLDB implementation of @see DAOTriageTag
 * @author Felix
 *
 */
public class DAOTriageTagHSQLDB implements DAOTriageTag {

	private static DAOTriageTagHSQLDB dt;
	private Connection c;
	private static Logger log = Logger.getLogger( DAOTriageTagHSQLDB.class );
	
	/**
	 * Creates new instance of DAOTriageTagHSQDLB
	 * @throws DAOException Thrown if database-connection fails.
	 */
	protected DAOTriageTagHSQLDB() throws DAOException {
		DatabaseManager dbm = DatabaseManager.getInstance();
		try {
			c = dbm.getConnection();
		} catch (NullPointerException e) {
			throw new DAOException("No Connection.");
		}
		dt = this;
		log.d("Instance created.");
		
	}

	/**
	 * Returns a new / existing instance of DAOTriageTagHSQLDB 
	 * @return Instance of DAOTriageTagHSQLDB
	 * @throws DAOException In case of SQL / connection errors.
	 */
	public static DAOTriageTag getInstance() throws DAOException  {

		if (dt == null) {
				return new DAOTriageTagHSQLDB();
		}
		return dt;
		
	}
	
	public void setAutoCommit(boolean autoCommit)
	{
		try 
		{
			c.setAutoCommit(autoCommit);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}
	
	public void rollback()
	{
		try 
		{
			c.rollback();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * @see DAOTriageTag#getTag(String)
	 */
	@Override
	public TriageTag getTag(String uid) throws DAOException {
		String query = "SELECT a.* FROM triagetag a,"
						+ " (SELECT max(document_vnr) AS max_vnr FROM  (SELECT * FROM triagetag WHERE document_uid = ?)) b"
						+ " WHERE a.document_uid = ? AND a.document_vnr = b.max_vnr;";
		PreparedStatement stmt;
		try {
			stmt = c.prepareStatement(query);
			
			stmt.setString(1, uid);
			stmt.setString(2, uid);
			
			ResultSet rs = stmt.executeQuery();
			TriageTag tt = null;
			if (rs.next()) {
				tt = constructFromResultSet(rs);
			}
			rs.close();
			stmt.close();
			return tt;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException(e.getMessage());
		}
	}

	/**
	 * @see DAOTriageTag#getTags(TriageCategory, TreatmentStatus)
	 */
	@Override
	public Set<TriageTag> getTags(TriageCategory triagekategorie,
			TreatmentStatus behandlung) throws DAOException {
		String query = "SELECT * FROM triagetag a," +
				" (SELECT document_uid, max(document_vnr) AS document_vnr FROM triagetag GROUP BY document_uid) b " +
				" WHERE a.document_uid = b.document_uid AND b.document_vnr = a.document_vnr";
		
		int counter = 0;
		boolean toReplace[] = {false, false};
		
		if (triagekategorie != null) {
			query += " AND triagekategorie = ?";
			toReplace[0] = true;
		}
		if (behandlung != null) {
			query += " AND behandlungstatus = ?";
			toReplace[1] = true;
		}
		
		query += ";";
		PreparedStatement stmt;
		
		try {
			stmt = c.prepareStatement(query);
			if (toReplace[0]) {
				stmt.setString(++counter, triagekategorie.toString());
			}
			
			if (toReplace[1]) {
				stmt.setString(++counter, behandlung.toString());
			}
			ResultSet rs = stmt.executeQuery();
			HashSet<TriageTag> tags = new HashSet<TriageTag>();
			while (rs.next()) {
				tags.add(constructFromResultSet(rs));
			}
			rs.close();
			stmt.close();
			return tags;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException(e.getMessage());
		}
		
	}

	/**
	 * @see DAOTriageTag#persist(TriageTag)
	 */
	@Override
	public void persist(TriageTag tag) throws DAOException {
		String queryMaxVer = "SELECT max(document_vnr) FROM triagetag WHERE document_uid = ?;";
		PreparedStatement stmt;
		try {
			log.d("Persisting Tag " + tag.getDocument_uid() + ", v:" + tag.getDocument_vnr() + "... ");
			log.d(tag.toString());
			stmt = c.prepareStatement(queryMaxVer);
			stmt.setString(1, tag.getDocument_uid());
			ResultSet rs = stmt.executeQuery();
			
			TriageTag toPersist = tag;
			
			if (rs.next() & rs.getString(1) != null) { //First time this document will be persistet ...
				log.d("Predecessor found ...");
				if (rs.getInt(1) >= tag.getDocument_vnr()) { //Not frist time but is newest ...
					
					//Now we have a problem ...
					log.d("Newer documents available, trying to integrate changes ...");
					
					//Get predecessor document and all newer documents
					String queryGetNewer = "SELECT * FROM triagetag WHERE document_uid = ? AND document_vnr >= ? ORDER BY document_vnr ASC;";
					stmt = c.prepareStatement(queryGetNewer);
					stmt.setString(1, tag.getDocument_uid()); //Same ID
					stmt.setInt(2, tag.getDocument_parent_vnr()); //Predecessor
					ResultSet rs2 = stmt.executeQuery();
					ArrayList<TriageTag> tags = new ArrayList<TriageTag>();
					
					//Construct array with document-history, oldest first
					while (rs2.next()) {
						tags.add(constructFromResultSet(rs2));
					}
					TriageTag[] tagArr = new TriageTag[tags.size()];
					tagArr = tags.toArray(tagArr);
					
					//Try to integrate changes in document-version-history
					toPersist = TriageTag.integrateChanges(tagArr, tag);
					
					//All the stored information is already "newer"
					if(toPersist == null) {
						log.d("Can't integrate, no new information in document.");
						return;
					}
					log.d("Document integrated successfully.");
				} 
			} 
			
			
			//Finally persist
			log.d("Persisting base-tag ...");
			stmt = c.prepareStatement("INSERT INTO triagetag VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
			stmt.setString(1,toPersist.getDocument_uid());
			stmt.setInt(2, toPersist.getDocument_vnr());
			stmt.setString(3, toPersist.getDocument_parent_uid());
			stmt.setInt(4, toPersist.getDocument_parent_vnr());
			stmt.setDate(5, new Date(toPersist.getEffectiveTime().getTimeInMillis()));
			stmt.setString(6, toPersist.getGiven());
			stmt.setString(7, toPersist.getFamily());
			stmt.setString(8, toPersist.getGender().toString());
			stmt.setDate(9, new Date(toPersist.getBirthdate().getTimeInMillis()));
			stmt.setString(10, toPersist.getAuthor_uid());
			stmt.setBoolean(11, toPersist.getGehfaehigkeit());
			
			//NOW OPTIONAL, COULD BE NULL
			if (toPersist.getRespiration() != null) {
				stmt.setString(12, toPersist.getRespiration().toString());
			} else {
				stmt.setNull(12, java.sql.Types.VARCHAR);
			}
			if (toPersist.getPerfusion() != null) {
				stmt.setString(13, toPersist.getPerfusion().toString());
			} else {
				stmt.setNull(13, java.sql.Types.VARCHAR);
			}
			if (toPersist.getMentalerstatus() != null) {
				stmt.setString(14, toPersist.getMentalerstatus().toString());
			} else {
				stmt.setNull(14, java.sql.Types.VARCHAR);
			}
			if (toPersist.getBehandlungsstatus() != null) {
				stmt.setString(15, toPersist.getBehandlungsstatus().toString());
			} else {
				stmt.setNull(15, java.sql.Types.VARCHAR);
			}
			if (toPersist.getTriagekategorie() != null) {
				stmt.setString(16, toPersist.getTriagekategorie().toString());
			} else {
				stmt.setNull(16, java.sql.Types.VARCHAR);
			}
			if (toPersist.getGps_longitude()!= null) {
				stmt.setDouble(17, toPersist.getGps_longitude());
			} else {
				stmt.setNull(17, java.sql.Types.DOUBLE);
			}
			if (toPersist.getGps_latitude()!= null) {
				stmt.setDouble(18, toPersist.getGps_latitude());
			} else {
				stmt.setNull(18, java.sql.Types.DOUBLE);
			}
			if (toPersist.getGps_accuracy()!= null) {
				stmt.setDouble(19, toPersist.getGps_accuracy());
			} else {
				stmt.setNull(19, java.sql.Types.NUMERIC);
			}
			if (toPersist.getLebensphase() != null) {
				stmt.setString(20, toPersist.getLebensphase().toString());
			} else {
				stmt.setNull(20, java.sql.Types.VARCHAR);
			}
			if (toPersist.getHilfplatzposition() != null) {
				stmt.setString(21, toPersist.getHilfplatzposition());
			} else {
				stmt.setNull(21, java.sql.Types.VARCHAR);
			}	
			if (toPersist.getBergeinformation() != null) {
				stmt.setString(22, toPersist.getBergeinformation());
			} else {
				stmt.setNull(22, java.sql.Types.LONGVARCHAR);
			}
			if (toPersist.getUrgency() != null) {
				stmt.setInt(23, toPersist.getUrgency());
			} else {
				stmt.setNull(23, java.sql.Types.INTEGER);
			}		
			if (toPersist.getDiagnosis() != null) {
				stmt.setString(24, toPersist.getDiagnosis());
			} else {
				stmt.setNull(24, java.sql.Types.LONGVARCHAR);
			}
			if (toPersist.getBlood_pressure_systolic() != null) {
				stmt.setInt(25, toPersist.getBlood_pressure_systolic());
			} else {
				stmt.setNull(25, java.sql.Types.INTEGER);
			}		
			if (toPersist.getBlood_pressure_diastolic() != null) {
				stmt.setInt(26, toPersist.getBlood_pressure_diastolic());
			} else {
				stmt.setNull(26, java.sql.Types.INTEGER);
			}		
			if (toPersist.getPuls() != null) {
				stmt.setInt(27, toPersist.getPuls());
			} else {
				stmt.setNull(27, java.sql.Types.INTEGER);
			}		
			if (toPersist.getBehandlungsverlauf() != null) {
				stmt.setString(28, toPersist.getBehandlungsverlauf());
			} else {
				stmt.setNull(28, java.sql.Types.LONGVARCHAR);
			}			
			if (toPersist.getTransportbereitschaft() != null) {
				stmt.setBoolean(29, toPersist.getTransportbereitschaft());
			} else {
				stmt.setNull(29, java.sql.Types.BOOLEAN);
			}
			if (toPersist.getZielkrankenhaus() != null) {
				stmt.setString(30, toPersist.getZielkrankenhaus());
			} else {
				stmt.setNull(30, java.sql.Types.VARCHAR);
			}			
			if (toPersist.getKrankenkasse() != null) {
				stmt.setString(31, toPersist.getKrankenkasse());
			} else {
				stmt.setNull(31, java.sql.Types.VARCHAR);
			}
			stmt.executeUpdate();

			//Add Bodyparts
			log.d("Persisting bodyparts ...");
			for (Entry<BodyPart, String> entry : toPersist.getVerletzungen().entrySet()) {
				PreparedStatement stmt2 = c.prepareStatement("INSERT INTO verletzung VALUES(?,?,?,?);");
				stmt2.setString(1, toPersist.getDocument_uid());
				log.d("UID: " + toPersist.getDocument_uid());
				stmt2.setInt(2, toPersist.getDocument_vnr());
				log.d("VNR: " + toPersist.getDocument_vnr());
				stmt2.setString(3,entry.getKey().toString());
				log.d("BODYPART: " + entry.getKey().toString());
				stmt2.setString(4, entry.getValue());
				log.d("INJURY: " + entry.getValue());
				stmt2.executeUpdate();
				stmt2.close();
			}
			
			stmt.close();
			rs.close();
			log.d("Persisting done.");
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException(e.getMessage());
		}
	}
	
	/**
	 * Constructs a new TriageTag from given ResultSet. On the assumption
	 * that the ResultSet contains a single TriageTag-Table-Row.
	 * @param rs ResultSet (with single row) to construct TriageTag-instance from
	 * @return A instance of TriageTag.
	 * @throws SQLException In case of SQL / Database-Errors
	 */
	private TriageTag constructFromResultSet(ResultSet rs) throws SQLException {
		
		//Create base-tag
		GregorianCalendar effectiveTime = new GregorianCalendar();
		effectiveTime.setTime(rs.getDate(5));
		
		Gender geschlecht = Gender.getFromString(rs.getString(8));
		
		GregorianCalendar birthDate = new GregorianCalendar();
		birthDate.setTime(rs.getDate(9));
		
		Stability respiration = Stability.getFromString(rs.getString(12));
		Stability perfusion = Stability.getFromString(rs.getString(13));
		Stability mentalerStatus = Stability.getFromString(rs.getString(14));
		TreatmentStatus behandlungsStatus = TreatmentStatus.getFromString(rs.getString(15));
		TriageCategory triagekategorie = TriageCategory.getFromString(rs.getString(16));
		
		PhaseOfLife lebensphase = PhaseOfLife.getFromString(rs.getString(20));
		
		TriageTag temp = new TriageTag(rs.getString(1),rs.getInt(2),rs.getString(3),rs.getInt(4),effectiveTime,rs.getString(6),rs.getString(7),geschlecht,birthDate,rs.getString(10),rs.getBoolean(11),respiration,perfusion,mentalerStatus,behandlungsStatus,triagekategorie,rs.getDouble(17),rs.getDouble(18),rs.getDouble(19),lebensphase,rs.getString(21),rs.getString(22),rs.getInt(23),rs.getString(24),rs.getInt(25),rs.getInt(26),rs.getInt(27),rs.getString(28),rs.getBoolean(29),rs.getString(30),rs.getString(31));
		
		return addInjuriesToTag(addAuthorNameToTag(temp));
	}
	
	/**
	 * Adds matching injuries to given TriageTag
	 * @param tag Tag to add injuries to
	 * @return Tag with applied injuries
	 * @throws SQLException In case of SQL / Database-Errors
	 */
	private TriageTag addInjuriesToTag(TriageTag tag) throws SQLException {
		String query = "SELECT koerperteil, verletzung FROM verletzung WHERE document_vnr = ? AND document_uid = ?";
		PreparedStatement stmt;
		stmt = c.prepareStatement(query);
		stmt.setString(2, tag.getDocument_uid());
		stmt.setInt(1, tag.getDocument_vnr());
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			tag.setVerletzung(BodyPart.getFromString(rs.getString(1)), rs.getString(2));
		}
		stmt.close();
		rs.close();
		return tag;
	}
	
	/**
	 * Adds matching author first and surname (given, family) to given TriageTag
	 * @param tag Tag to add author-info to
	 * @return Tag with applied author's first- and surname
	 * @throws SQLException In case of SQL / Database-Errors
	 */
	private TriageTag addAuthorNameToTag(TriageTag tag) throws SQLException {
		String query = "SELECT given, family FROM user WHERE uid = ?";
		PreparedStatement stmt;
		stmt = c.prepareStatement(query);
		stmt.setString(1, tag.getAuthor_uid());
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			tag.setAuthor_given(rs.getString(1));
			tag.setAuthor_family(rs.getString(2));
		}
		stmt.close();
		rs.close();
		return tag;
	}
}
